package com.dao.impl;

import com.dao.OrderDao;
import com.entity.Orders;
import com.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class OrderDaoImpl implements OrderDao {
    QueryRunner runner = new QueryRunner();
    @Override
    public List<Orders> listOrderById(int id) {
        Connection connection = DruidUtils.getConnection();
        String sql="SELECT o.id,number,money,`name`,photo,oNumber,state from orders as o,foods WHERE customers_id=? and food_id=foods.id and state=0";
        try {
            List<Orders> query = runner.query(connection, sql, new BeanListHandler<>(Orders.class), id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int upDateState(String oNumber) {
        Connection connection = DruidUtils.getConnection();
        String sql = "UPDATE orders set state=1 WHERE oNumber =?";
        try {
            int update = runner.update(connection, sql, oNumber);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public List<Map<String, Object>> queryOrderDetail(String oNumber) {
        Connection connection = DruidUtils.getConnection();
        String sql="SELECT * FROM orders,foods WHERE orders.food_id=foods.ID AND orders.oNumber=?;";
        try {
            List<Map<String, Object>> foodsDetail = runner.query(connection, sql,new MapListHandler(),oNumber);
            return foodsDetail;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<Orders> queryOrderAll() {
        Connection connection = DruidUtils.getConnection();
        String sql="SELECT * FROM orders;";
        try {
            List<Orders> query = runner.query(connection, sql, new BeanListHandler<>(Orders.class));
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int queryOrderAllByUserId() {
        Connection connection = DruidUtils.getConnection();
        String sql="SELECT COUNT(distinct oNumber) FROM orders;";
        try {
            Number date=runner.query(connection,sql,new ScalarHandler<>());
            int count = date.intValue();
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    public static void main(String[] args) {
        OrderDao orderDao=new OrderDaoImpl();
        int orders = orderDao.queryOrderAllByUserId();
        System.out.println(orders);
    }
}
